package quipu.utils;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import com.mysql.jdbc.PreparedStatement;

public class DB {
	private String host;
	private String user;
	private String pass;
	private String database;
	private Connection conn;
	private boolean conexion;
	
	public DB() throws SQLException{
		this.host="localhost";
		this.user="root";
		this.pass="pass";
		this.database="squalesh";
		open();
	}
	
	public DB(String user, String pass, String database) throws SQLException {
		this.user = user;
		this.pass = pass;
		this.database = database;
		open();
	}
	
	private void open() throws SQLException{
		try {
			Class.forName("com.mysql.jdbc.Driver").newInstance();
		} catch (InstantiationException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IllegalAccessException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String urljdbc = "jdbc:mysql://" + this.host + ":" + 3306 + "/" + this.database;
		this.conn = DriverManager.getConnection(urljdbc,this.user,this.pass);
		conexion = true;
		
	}
	
	public void close() throws SQLException{
		this.conn.close();
		conexion = false;
	}
	
	public ResultSet execute(PreparedStatement p) throws SQLException{
		if(!conexion)
			open();
		ResultSet rs = p.executeQuery();
		return rs;
	}
	
	public ResultSet execute(String consulta) throws SQLException{
		if(!conexion)
			open();
		Statement stm = this.conn.createStatement();
		ResultSet rs = stm.executeQuery(consulta);
		
		return rs;
	}
	
	public void executeProcedureById(String type, String procedure, Integer id, String others) throws SQLException{
		if(!conexion)
			open();
		Statement stm = this.conn.createStatement();
		String structure = null;
		try{
			if(type.equalsIgnoreCase("delete"))
				structure = "CALL " + procedure + "(" + id + ")";
			else if(type.equalsIgnoreCase("create")){
				structure = "CALL " + procedure + "(" + others + ")";
				
				System.out.println(structure);
			}
			else if(type.equalsIgnoreCase("modify")){
				
			}
								
			stm.executeUpdate(structure);
		}
		catch(SQLException e){
			e.printStackTrace();
		}
		return ;
	}
	
	public ResultSet executeRecord(String nomTab, String filtro) throws SQLException{
		if(!conexion)
			open();
		Statement stm = this.conn.createStatement();
		//System.out.print("SELECT * FROM "+nomTab+" WHERE " + filtro);
		ResultSet rs = stm.executeQuery("SELECT * FROM "+nomTab+" WHERE " + filtro);
		
		return rs;
		
	}
	
	public ResultSet executeField(String campo, String nomTab, String filtro) throws SQLException{
		if(!conexion)
			open();
		Statement stm = this.conn.createStatement();
		
		//System.out.println("SELECT "+campo+" FROM "+nomTab+" WHERE " + filtro);
		

		ResultSet rs = stm.executeQuery("SELECT "+campo+" FROM "+nomTab+" WHERE " + filtro);
	
		return rs;
		
	}
	
	public ResultSet executeField(String campo, String nomTab) throws SQLException{
		if(!conexion)
			open();
		Statement stm = this.conn.createStatement();
		ResultSet rs = stm.executeQuery("SELECT "+campo+" FROM "+nomTab);
	
		return rs;
	}
	
	public ResultSet executeTable (String nomTab, String orden) throws SQLException{
		if(!conexion)
			open();
		Statement stm = this.conn.createStatement();
		ResultSet rs = stm.executeQuery("SELECT * FROM " + nomTab+ " ORDER BY "+ orden);
	
		
		return rs;
	}
	
	public ResultSet executeTable (String nomTab) throws SQLException{
		if(!conexion)
			open();
		Statement stm = this.conn.createStatement();
		ResultSet rs = stm.executeQuery("SELECT * FROM " + nomTab);
	
		return rs;
	}
	
	public void executeScalar(String sentencia) throws SQLException{
		if(!conexion)
			open();
		Statement stm = this.conn.createStatement();
		stm.executeUpdate(sentencia);
		
	}
	
	public int recordCount(String nombreTabla) throws SQLException{
		if(!conexion)
			open();
		
		ResultSet rs = this.execute("SELECT COUNT(*) FROM " + nombreTabla);
		rs.next();
		int count = rs.getInt(1);
		
		return count;
	}
	
	public void executeInsert(String tabla, String valores) throws SQLException{
		if(!conexion)
			open();
		Statement stm = this.conn.createStatement();
		String consulta = "INSERT INTO "+tabla+" VALUES ("+valores+");";
		System.out.print(consulta);
		stm.executeUpdate(consulta);
		
	}
	
	public void delete(String tabla,String condicion) throws SQLException{
		if(!conexion)
			open();
		Statement stm = this.conn.createStatement();
		String consulta = "DELETE FROM "+tabla+" WHERE "+condicion;
		stm.executeUpdate(consulta);
		
	}
	public void update(String tabla,String valores,String condicion) throws SQLException{
		if(!conexion)
			open();
		Statement stm = this.conn.createStatement();
		String updte = "UPDATE "+tabla+" SET "+valores+" WHERE "+condicion;
		System.out.println(updte);
		stm.executeUpdate(updte);
		
	}
	
	public int getLasID() throws SQLException{
		if(!conexion)
			open();
		Statement stm = this.conn.createStatement();
		ResultSet rs = stm.executeQuery("SELECT LAST_INSERT_ID()");
		rs.next();
		int las = rs.getInt(1);
		
		return las;
		
	}
	public int getLasID(String tabla) throws SQLException{
		if(!conexion)
			open();
		Statement stm = this.conn.createStatement();
		ResultSet rs = stm.executeQuery("SELECT LAST_INSERT_ID() FROM " + tabla);
		rs.next();
		int last = rs.getInt(1); 
		

		return last;
		
	}
	
	public ResultSet ultimoDato(String columna,String tabla) throws SQLException{
		if(!conexion)
			open();
		Statement stm = this.conn.createStatement();
		ResultSet rs = stm.executeQuery("SELECT MAX("+columna+") AS "+columna+" FROM "+tabla+"");
	
		return rs;
	}
	
	public void reset() throws SQLException{
		this.conn.setAutoCommit(true);
		
	}
	
	public Connection getConeccion(){
		return this.conn;
	}

	}
